﻿using Dapper;
using Entity;
using IRepository;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace Repository
{
    public class InboundOrderRepository : IInboundOrderRepository
    {
        private readonly IDbConnection _dbConnection;
        private readonly ILogger<InboundOrderRepository> _logger;

        public InboundOrderRepository(IDbConnection dbConnection, ILogger<InboundOrderRepository> logger)
        {
            _dbConnection = dbConnection;
            _logger = logger;
        }
        /// <summary>
        /// 插入主表和明细表（适配你的实体字段与数据库表映射）
        /// </summary>
        public async Task AddInboundOrderWithDetailsAsync(InboundOrderEntity order, List<InboundOrderDetailEntity2> details)
        {
            using var transaction = BeginTransaction();
            try
            {
                // 1. 插入入库单主表（保持不变）
                var mainSql = @"
                    INSERT INTO dbo.wms_knzy_stock_in (
                        warehouse_id, rule_id, type_id, warehouse_receipt_no, storage_type,
                        warehouse_name, storage_status, date_of_storage, associated_order_no,
                        suppliers_id, handler_id, organizations_id, reviewer, note,
                        create_by, create_time, is_delete
                    )
                    VALUES (
                        @warehouse_id, @rule_id, @type_id, @warehouse_receipt_no, @storage_type,
                        @warehouse_name, @storage_status, @date_of_storage, @associated_order_no,
                        @suppliers_id, @handler_id, @organizations_id, @reviewer, @note,
                        @create_by, @create_time, @is_delete
                    );
                    SELECT CAST(SCOPE_IDENTITY() AS INT);
                ";
                var warehouseReceiptId = await _dbConnection.QueryFirstAsync<int>(mainSql, order, transaction);
                
                // 2. 插入明细表（适配InboundOrderDetailEntity2的字段）
                var detailSql = @"
                    INSERT INTO dbo.wms_knzy_warehousing_details (
                        warehouse_receipt_id,     
                        product_code,             
                        unit_price,               
                        curr_stk_in_num,          
                        batch_number,             
                        Production_date,          
                        note,                     
                        storage_location,         
                        create_by, 
                        create_time, 
                        update_by, 
                        update_time, 
                        is_delete
                    )
                    VALUES (
                        @warehouse_receipt_id,
                        @product_code,
                        @unit_price,                
                        @curr_stk_in_num,           
                        @batch_number,              
                        @Production_date,           
                        @note,
                        @storage_location,          
                        @create_by,
                        @create_time,
                        @update_by,
                        @update_time,
                        @is_delete
                    )
                ";

                foreach (var detail in details)
                {
                    detail.warehouse_receipt_id = warehouseReceiptId; // 绑定主表ID
                    await _dbConnection.ExecuteAsync(detailSql, detail, transaction);
                }

                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        public async Task AddInboundOrderWithDetailsAsync2(InboundOrderEntity order, List<InboundOrderDetailEntity2> details)
        {
            _logger.LogInformation("开始执行入库单添加，连接状态：{State}", _dbConnection.State);
            try
            {
                if (_dbConnection.State == ConnectionState.Closed)
                {
                    _dbConnection.Open();
                    _logger.LogInformation("连接已打开，状态：{State}", _dbConnection.State);
                }

                using var tran = _dbConnection.BeginTransaction();
                _logger.LogInformation("事务已开始，连接状态：{State}", _dbConnection.State);
                try
                {
                    // 1. 插入主表（表名正确，无需修改）
                    var mainSql = @"
                INSERT INTO dbo.wms_knzy_stock_in (
                    warehouse_id, rule_id, type_id, warehouse_receipt_no, storage_type,
                    warehouse_name, storage_status, date_of_storage, associated_order_no,
                    suppliers_id, handler_id, organizations_id, reviewer, note,
                    create_by, create_time, is_delete
                )
                VALUES (
                    @warehouse_id, @rule_id, @type_id, @warehouse_receipt_no, @storage_type,
                    @warehouse_name, @storage_status, @date_of_storage, @associated_order_no,
                    @suppliers_id, @handler_id, @organizations_id, @reviewer, @note,
                    @create_by, @create_time, @is_delete
                );
                SELECT CAST(SCOPE_IDENTITY() AS INT);
            ";
                    var warehouseReceiptId = await _dbConnection.QueryFirstAsync<int>(mainSql, order, tran);
                    _logger.LogInformation("主表插入完成，ID：{Id}", warehouseReceiptId);

                    // 2. 插入明细表（修正表名为wms_knzy_warehousing_details，匹配数据库结构）
                    var detailSql = @"
                INSERT INTO dbo.wms_knzy_warehousing_details (
                    warehouse_receipt_id,     
                    product_code,             
                    unit_price,               
                    curr_stk_in_num,          
                    batch_number,             
                    Production_date,          
                    note,                     
                    storage_location,         
                    create_by, 
                    create_time, 
                    update_by, 
                    update_time, 
                    is_delete
                )
                VALUES (
                    @warehouse_receipt_id,
                    @product_code,
                    @unit_price,                
                    @curr_stk_in_num,           
                    @batch_number,              
                    @Production_date,           
                    @note,
                    @storage_location,          
                    @create_by,
                    @create_time,
                    @update_by,
                    @update_time,
                    @is_delete
                )
            ";

                    var now = DateTime.Now;
                    foreach (var detail in details)
                    {
                        detail.warehouse_receipt_id = warehouseReceiptId;
                        detail.create_by = order.create_by;
                        detail.create_time = now;
                        detail.update_by = order.create_by;
                        detail.update_time = now;
                        detail.is_delete = "0";
                        detail.unit_price = detail.unit_price; // 确保单价有默认值
                        await _dbConnection.ExecuteAsync(detailSql, detail, tran);
                        _logger.LogInformation("明细插入完成，货品编号：{Code}，数量：{Qty}", detail.product_code, detail.curr_stk_in_num);
                    }

                    tran.Commit();
                    _logger.LogInformation("事务提交成功，连接状态：{State}", _dbConnection.State);
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    _logger.LogError(ex, "事务回滚，原因：{Message}", ex.Message);
                    throw;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "入库单添加失败，连接状态：{State}，原因：{Message}", _dbConnection.State, ex.Message);
                throw;
            }
            finally
            {
                _logger.LogInformation("操作完成，最终连接状态：{State}", _dbConnection.State);
            }
        }

        public IDbTransaction BeginTransaction()
        {
            if (_dbConnection.State == ConnectionState.Closed)
                _dbConnection.Open();
            return _dbConnection.BeginTransaction();
        }
        
        public async Task<InboundOrderEntity> GetByIdAsync(int warehouseReceiptId, IDbTransaction transaction)
        {
            var sql = "SELECT * FROM wms_knzy_stock_in WHERE warehouse_receipt_id = @WarehouseReceiptId AND is_delete = '0'";
            return await _dbConnection.QueryFirstOrDefaultAsync<InboundOrderEntity>(
                sql,
                new { WarehouseReceiptId = warehouseReceiptId },
                transaction
            );
        }

        public async Task<PurchaseOrderEntity2> GetByOrderNoAsync(string purchaseOrderNo)
        {
            var sql = "SELECT * FROM wms_order_purchase_order WHERE purchase_order_no = @PurchaseOrderNo AND is_delete = '0'";
            // 映射到新实体类
            return await _dbConnection.QueryFirstOrDefaultAsync<PurchaseOrderEntity2>(sql, new { PurchaseOrderNo = purchaseOrderNo });
        }

        public async Task<List<PurchaseOrderDetailEntity>> GetByPurchaseIdAsync(int purchaseId)
        {
            var sql = "SELECT * FROM wms_order_details WHERE purchase_id = @PurchaseId AND is_delete = '0'";
            return (await _dbConnection.QueryAsync<PurchaseOrderDetailEntity>(sql, new { PurchaseId = purchaseId })).ToList();
        }

        public async Task<List<InboundOrderDetailEntity2>> GetDetailsByReceiptIdAsync(int warehouseReceiptId, IDbTransaction transaction)
        {
            var sql = "SELECT * FROM wms_knzy_warehousing_details WHERE warehouse_receipt_id = @WarehouseReceiptId AND is_delete = '0'";
            return (await _dbConnection.QueryAsync<InboundOrderDetailEntity2>(
                sql,
                new { WarehouseReceiptId = warehouseReceiptId },
                transaction
            )).ToList();
        }

        public async Task<List<InboundQueryResult>> GetInboundListAsync(InboundQueryParam param)
        {
            var sql = @"
                            SELECT 
                si.warehouse_receipt_id AS warehouse_receipt_id, -- 新增：入库单主键ID
                si.warehouse_receipt_no AS purchase_order_no,  
                si.type_id AS document_statu,                  
                si.storage_status AS status,                   
                si.date_of_storage AS purchase_date,           
                wh.warehouse_name AS contact_person,           
                si.associated_order_no AS contact_information, 
                STUFF((
                    SELECT ',' + pi.product_name 
                    FROM dbo.wms_knzy_warehousing_details detail_pi
                    JOIN dbo.wms_basic_product_information pi ON detail_pi.product_code = pi.product_code
                    WHERE detail_pi.warehouse_receipt_id = si.warehouse_receipt_id
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS organizations_id,  
                handler.user_name AS purchaser,                
                SUM(detail.curr_stk_in_num) AS totalQuantity,  
                SUM(detail.curr_stk_in_num * detail.unit_price) AS totalAmount,  
                creator.user_name AS productNames,             
                org.organization_name AS create_by,           
                si.create_time AS create_time,                 
                auditor.user_name AS reviewer,                 
                si.review_time AS review_time                  
            FROM dbo.wms_knzy_stock_in si
            JOIN dbo.wms_knzy_warehousing_details detail ON si.warehouse_receipt_id = detail.warehouse_receipt_id
            JOIN dbo.wms_basic_warehouse wh ON si.warehouse_id = wh.warehouse_id
            JOIN dbo.sys_org_user_management creator ON si.create_by = creator.user_account
            JOIN dbo.sys_org_user_management handler ON si.handler_id = handler.user_id
            JOIN dbo.sys_org_organizations org ON creator.organization_id = org.organization_id
            LEFT JOIN dbo.sys_org_user_management auditor ON si.reviewer = auditor.user_account
            WHERE si.is_delete = '0' AND detail.is_delete = '0'  ";

            var whereConditions = new List<string>();
            var paramDict = new DynamicParameters();

            // 拼接筛选条件
            if (!string.IsNullOrEmpty(param.WarehouseReceiptNo))
            {
                whereConditions.Add("si.warehouse_receipt_no LIKE @WarehouseReceiptNo");
                paramDict.Add("WarehouseReceiptNo", $"%{param.WarehouseReceiptNo}%");
            }
            if (param.DocumentStatu.HasValue)
            {
                whereConditions.Add("si.type_id = @DocumentStatu");
                paramDict.Add("DocumentStatu", param.DocumentStatu.Value);
            }
            if (!string.IsNullOrEmpty(param.Status))
            {
                whereConditions.Add("si.storage_status = @Status");
                paramDict.Add("Status", param.Status);
            }
            if (param.StartDate.HasValue)
            {
                whereConditions.Add("si.date_of_storage >= @StartDate");
                paramDict.Add("StartDate", param.StartDate.Value);
            }
            if (param.EndDate.HasValue)
            {
                whereConditions.Add("si.date_of_storage <= @EndDate");
                paramDict.Add("EndDate", param.EndDate.Value);
            }

            if (whereConditions.Count > 0)
            {
                sql += " AND " + string.Join(" AND ", whereConditions);
            }

            // 分组与排序
            sql += @"
                GROUP BY 
                    si.warehouse_receipt_id, 
                    si.warehouse_receipt_id, 
                    si.warehouse_receipt_no, 
                    si.type_id, 
                    si.storage_status, 
                    si.date_of_storage, 
                    wh.warehouse_name, 
                    si.associated_order_no, 
                    handler.user_name, 
                    creator.user_name, 
                    org.organization_name, 
                    si.create_time, 
                    auditor.user_name, 
                    si.review_time 
                ORDER BY si.create_time DESC";

            return (await _dbConnection.QueryAsync<InboundQueryResult>(sql, paramDict)).ToList();
        }

        public async Task<int> GetInboundTotalAsync(InboundQueryParam param)
        {
            var sql = @"
                SELECT COUNT(DISTINCT si.warehouse_receipt_no) 
                FROM dbo.wms_knzy_stock_in si
                JOIN dbo.wms_knzy_warehousing_details detail ON si.warehouse_receipt_id = detail.warehouse_receipt_id
                WHERE si.is_delete = '0' AND detail.is_delete = '0'
                ";

            var whereConditions = new List<string>();
            var paramDict = new DynamicParameters();

            // 与查询列表一致的筛选条件
            if (!string.IsNullOrEmpty(param.WarehouseReceiptNo))
            {
                whereConditions.Add("si.warehouse_receipt_no LIKE @WarehouseReceiptNo");
                paramDict.Add("WarehouseReceiptNo", $"%{param.WarehouseReceiptNo}%");
            }
            if (param.DocumentStatu.HasValue)
            {
                whereConditions.Add("si.type_id = @DocumentStatu");
                paramDict.Add("DocumentStatu", param.DocumentStatu.Value);
            }
            if (!string.IsNullOrEmpty(param.Status))
            {
                whereConditions.Add("si.storage_status = @Status");
                paramDict.Add("Status", param.Status);
            }
            if (param.StartDate.HasValue)
            {
                whereConditions.Add("si.date_of_storage >= @StartDate");
                paramDict.Add("StartDate", param.StartDate.Value);
            }
            if (param.EndDate.HasValue)
            {
                whereConditions.Add("si.date_of_storage <= @EndDate");
                paramDict.Add("EndDate", param.EndDate.Value);
            }

            if (whereConditions.Count > 0)
            {
                sql += " AND " + string.Join(" AND ", whereConditions);
            }

            return await _dbConnection.QueryFirstAsync<int>(sql, paramDict);
        }

        public async Task UpdateAsync(InboundOrderEntity entity, IDbTransaction transaction)
        {
            var sql = @"
                UPDATE wms_knzy_stock_in 
                SET storage_status = @storage_status, 
                    reviewer = @reviewer, 
                    review_time = @review_time, 
                    note = @note, 
                    update_time = GETDATE()
                WHERE warehouse_receipt_id = @warehouse_receipt_id";
            await _dbConnection.ExecuteAsync(
                sql,
                entity,
                transaction
            );
        }
    }
}
